package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NoticeUtil implements NoticeDao {
	private Connection conn;
	private PreparedStatement noticeQuery;
	private ResultSet rs;
	@Override
	public List<Notice> getNotices() {
		List<Notice> noticeList=new ArrayList<Notice>();
		try {
			conn=DBcon.getConnection();
			String sql="select id,notice from notice order by id ";
			noticeQuery=conn.prepareStatement(sql);
			ResultSet rs=noticeQuery.executeQuery();
			while(rs.next()) {
				Notice notice=new Notice();
				notice.setNotice(rs.getString("notice"));
				notice.setId(rs.getInt("id"));
				noticeList.add(notice);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(noticeQuery);
		}
		return noticeList;
	}
		
	

	@Override
	public int add(Notice notice) {
		int result=0;
		try {
		conn=DBcon.getConnection();
		String sql="insert into notice (id,notice) values(?,?)";
		noticeQuery=conn.prepareStatement(sql);
		noticeQuery.setInt(1,notice.getId());
		noticeQuery.setString(2,notice.getNotice());
		
		result=noticeQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(noticeQuery);
		}
		return result;
	}

	@Override
	public int delete(int id) {
		int result=0;
		try {
			conn=DBcon.getConnection();
			String sql="delete from notice where id='"+id+"'";
			noticeQuery=conn.prepareStatement(sql);
			result=noticeQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(noticeQuery);
		}
		return result;

}
}